package DBLayer;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.Statement;

public class DBLevyRate implements IFDBLevyRate {
	private Connection con;
	private static DBLevyRate dbLR;
	
	private DBLevyRate() {
		con = DBConnection.getInstance().getDBcon();
	}
	
	public static DBLevyRate getInstance(){
		if(dbLR==null) dbLR = new DBLevyRate();
		return dbLR;
	}
	
	
	
	
	@Override
	public double getLevy(int noOfRooms, int buildingId) {
		String query = "SELECT levyAmount FROM LevyValues WHERE noOfRooms="+noOfRooms+" AND zone=(SELECT zone FROM Building WHERE buildingNr="+buildingId+")";
		
		double res=0;
		
		ResultSet result;
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			result = stmt.executeQuery(query);
			
			if(result.next()){
				res = result.getDouble("levyAmount");
			}
			
			stmt.close();
			
		}
		catch(Exception e){
			System.err.println("Could not execute retrieve the levy amount "+e.getMessage());
		}
		
		return res;
	}
	
	@Override
	public double getRateZone(int buildingId) {
		String query = "SELECT rateZoneAmount FROM RateZoneValue WHERE zone=(SELECT zone FROM Building WHERE buildingNr="+buildingId+")";
		
		
		double res=0;
		
		ResultSet result;
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			result = stmt.executeQuery(query);
			
			if(result.next()){
				res = result.getDouble("rateZoneAmount");
			}
			
			stmt.close();
			
		}
		catch(Exception e){
			System.err.println("Could not retrieve the rate zone amount "+e.getMessage());
		}
		
		return res;
	}
	
	@Override
	public double getRateRooms(int noOfRooms) {
		String query = "SELECT rateAmount FROM RateValues WHERE noOfRooms="+noOfRooms;
		
		double res=0;
		
		ResultSet result;
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			result = stmt.executeQuery(query);
			
			if(result.next()){
				res = result.getDouble("rateAmount");
			}
			
			stmt.close();
			
		}
		catch(Exception e){
			System.err.println("Could not retrieve the rate noOfRooms amount "+e.getMessage());
		}
		
		return res;
	}
	
	@Override
	public void insertRateZone(int buildingNr, double rateZoneAmount){
		int rc=-1;
		
		String queryInsertRateZone = "INSERT INTO RateZoneValue (zone,rateZoneAmount) VALUES ((SELECT zone FROM Building WHERE buildingNr="+buildingNr+"),"+rateZoneAmount+")";
		String queryUpdateRateZone = "UPDATE RateZoneValue SET rateZoneAmount="+rateZoneAmount+" WHERE zone=(SELECT zone FROM Building WHERE buildingNr="+buildingNr+")";
		
		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			
			rc = stmt.executeUpdate(queryUpdateRateZone);
			if(rc==0)
				rc = stmt.executeUpdate(queryInsertRateZone);

			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not insert the location  "+e.getMessage());
		}
		
	}
	
	
	@Override
	public void insertRateRooms(int noOfRooms, double rateAmount){
		int rc=-1;
		
		String queryInsertRateRooms = "INSERT INTO RateValues (noOfRooms,rateAmount) VALUES ("+noOfRooms+","+rateAmount+")";
		String queryUpdateRateRooms = "UPDATE RateValues SET rateAmount="+rateAmount+" WHERE noOfRooms="+noOfRooms;
		

		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			
			rc = stmt.executeUpdate(queryUpdateRateRooms);
			if(rc==0)
				rc = stmt.executeUpdate(queryInsertRateRooms);

			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not insert the location  "+e.getMessage());
		}
		
	}
	
	
	@Override
	public void insertLevy(int noOfRooms, double levyAmount, int buildingNr){
		int rc=-1;
		
		String queryInsertLevy = "INSERT INTO LevyValues (noOfRooms,zone,levyAmount) VALUES ("+noOfRooms+",(SELECT zone FROM Building WHERE buildingNr="+buildingNr+") , "+levyAmount+")";
		String queryUpdateLevy = "UPDATE LevyValues SET levyAmount="+levyAmount+" WHERE noOfRooms="+noOfRooms+" AND zone=(SELECT zone FROM Building WHERE buildingNr="+buildingNr+")";
		


		try{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			
			rc = stmt.executeUpdate(queryUpdateLevy);
			if(rc==0)
				rc = stmt.executeUpdate(queryInsertLevy);

			stmt.close();
		}
		catch(Exception e){
			System.err.println("Could not insert the location  "+e.getMessage());
		}
		
	}
	
	
	
}
